package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.dto.CarCartrainInnerDto;
import com.tianqu.tms.www.statistics.dto.ComboDto;

public class TmsMarketCarStyleCountDao extends TmsDao{

	public TmsMarketCarStyleCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 车系TOP5
	 * @return
	 * @throws TmsDaoException
	 */
	public List<ComboDto> getCarStyleTop5() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ComboDto> tmsMarketCarStyleCountList = new ArrayList<ComboDto>();

		try {

			StringBuffer sql = new StringBuffer(" SELECT t.brandName, t.onlineCount from ( ");
			sql.append(" SELECT ");
			sql.append(" brandName,");
			sql.append(" IFNULL(SUM(onlineCount), 0) AS onlineCount");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CARSTYLE_DAY_COUNT);
			sql.append(" WHERE countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y-%m-%d')");
			sql.append(" AND date_format(CURDATE(),'%Y-%m-%d')");
			sql.append(" GROUP BY brandId");
			sql.append(" ORDER BY onlineCount DESC");
			sql.append(" LIMIT 5");
			sql.append("  ) t order by t.onlineCount ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				ComboDto dto = new ComboDto();
				dto.setName(rs.getString("brandName"));
				dto.setValue(rs.getString("onlineCount"));
				tmsMarketCarStyleCountList.add(dto);
			}

			return tmsMarketCarStyleCountList;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 品牌分布-在库&出库
	 * @return
	 * @throws TmsDaoException
	 */
	public Map<String, CarCartrainInnerDto> getCarsCartrain(String startDate, String endDate) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Map<String, CarCartrainInnerDto> carCartrainMap = new HashMap<String, CarCartrainInnerDto>();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" CONCAT(DATE_FORMAT(tmp_info.cDate,'%Y-%m-%d'),'-',tmp_type.brandId) mapKey,");
			sql.append(" tmp_type.brandId,");
			sql.append(" tmp_type.brandName,");
			sql.append(" tmp_info.cDate,");
			sql.append(" tmp_info.onlineCount,");
			sql.append(" tmp_info.offlineCount ");
			sql.append(" FROM ");
			sql.append("(");
			sql.append(" select distinct tmcdc.brandId,tmcdc.brandName ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CARSTYLE_DAY_COUNT);
			sql.append(" tmcdc ");
			sql.append("order by tmcdc.brandId");
			sql.append(") tmp_type ");
			sql.append(" left join ( ");
			sql.append(" select DATE_FORMAT(tmcdc.countDate,'%Y-%m-%d') cDate, ");
			sql.append(" brandId, ");
			sql.append(" onlineCount, ");
			sql.append(" offlineCount ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CARSTYLE_DAY_COUNT);
			sql.append(" tmcdc ");
			sql.append(" where DATE_FORMAT(tmcdc.countDate,'%Y-%m-%d') BETWEEN ? and ? ");
			sql.append(" order by CONCAT(DATE_FORMAT(tmcdc.countDate,'%Y-%m-%d'),brandId) ");
			sql.append(" ) tmp_info on tmp_type.brandId = tmp_info.brandId  ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, startDate);
			ps.setString(2, endDate);
			rs = ps.executeQuery();

			while (rs.next()) {
				CarCartrainInnerDto dto = new CarCartrainInnerDto();
				dto.setcDate(rs.getString("cDate"));
				dto.setBrandId(rs.getString("brandId"));
				dto.setBrandName(rs.getString("brandName"));
				dto.setOnlineCount(rs.getString("onlineCount"));
				dto.setOfflineCount(rs.getString("offlineCount"));
				
				carCartrainMap.put(rs.getString("mapKey"), dto);
			}

			return carCartrainMap;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
}
